using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Models;

namespace DAL
{
    public static partial class MayorLocationService
    {
        public static bool AddMayorLocation(MayorLocation mayorLocation)
        {
            string sql =
                "INSERT MayorLocation (Location, IconID, MayorID)" +
                "VALUES (@Location, @IconID, @MayorID)";

            sql += " ; SELECT @@IDENTITY";


            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@Location", mayorLocation.Location),
				new SqlParameter("@IconID", mayorLocation.IconID),
				new SqlParameter("@MayorID", mayorLocation.MayorID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }


        public static bool DeleteMayorLocationByID(int iD)
        {
            string sql = "DELETE MayorLocation WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        public static bool ModifyMayorLocation(MayorLocation mayorLocation)
        {
            string sql =
                "UPDATE MayorLocation " +
                "SET " +
                    "Location = @Location, " +
                    "IconID = @IconID, " +
                    "MayorID = @MayorID " +
                "WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", mayorLocation.ID),
				new SqlParameter("@Location", mayorLocation.Location),
				new SqlParameter("@IconID", mayorLocation.IconID),
				new SqlParameter("@MayorID", mayorLocation.MayorID)
			};

            int i = DBHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public static IList<MayorLocation> GetAllMayorLocations()
        {
            string sqlAll = "SELECT * FROM MayorLocation";
            return GetMayorLocationsBySql(sqlAll);
        }

        public static IList<MayorLocation> GetMayorLocationsByMayorID(int mayorID)
        {
            string sqlAll = "SELECT * FROM MayorLocation where MayorID=@MayorID and Location <> ''";
            SqlParameter[] para = new SqlParameter[]
			{
                new SqlParameter("@MayorID", mayorID)
			};
            return GetMayorLocationsBySql(sqlAll, para);
        }

        public static MayorLocation GetMayorLocationByIDAndV(int iD, int videoID)
        {
            string sql = "SELECT * FROM MayorLocation WHERE IconID = @IconID and MayorID=@MayorID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@IconID", iD),
                new SqlParameter("@MayorID", videoID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            MayorLocation mayorLocation = new MayorLocation();

            foreach (DataRow dr in temp.Rows)
            {
                mayorLocation.ID = (int)dr["ID"];
                mayorLocation.Location = (string)dr["Location"];
                mayorLocation.IconID = (int)dr["IconID"];
                mayorLocation.MayorID = (int)dr["MayorID"];

            }
            return mayorLocation;

        }

        public static MayorLocation GetMayorLocationByID(int iD)
        {
            string sql = "SELECT * FROM MayorLocation WHERE ID = @ID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@ID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            MayorLocation mayorLocation = new MayorLocation();

            foreach (DataRow dr in temp.Rows)
            {
                mayorLocation.ID = (int)dr["ID"];
                mayorLocation.Location = (string)dr["Location"];
                mayorLocation.IconID = (int)dr["IconID"];
                mayorLocation.MayorID = (int)dr["MayorID"];

            }
            return mayorLocation;

        }

        public static MayorLocation GetMayorLocationByIDAndIconID(int mayorID, int iconID)
        {
            string sql = "SELECT * FROM MayorLocation WHERE MayorID = @MayorID and IconID=@IconID";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@MayorID", mayorID),
                new SqlParameter("@IconID", iconID)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            MayorLocation mayorLocation = new MayorLocation();

            foreach (DataRow dr in temp.Rows)
            {
                mayorLocation.ID = (int)dr["ID"];
                mayorLocation.Location = (string)dr["Location"];
                mayorLocation.IconID = (int)dr["IconID"];
                mayorLocation.MayorID = (int)dr["MayorID"];

            }
            return mayorLocation;

        }

        public static MayorLocation GetMayorLocationBytop1(int iD)
        {
            string sql = "SELECT top 1 * FROM MayorLocation WHERE MayorID = @MayorID and Location <> ''";

            SqlParameter[] para = new SqlParameter[]
			{
				new SqlParameter("@MayorID", iD)
			};

            DataTable temp = DBHelper.ExecuteGetDataTable(CommandType.Text, sql, para);

            MayorLocation mayorLocation = new MayorLocation();

            foreach (DataRow dr in temp.Rows)
            {
                mayorLocation.ID = (int)dr["ID"];
                mayorLocation.Location = (string)dr["Location"];
                mayorLocation.IconID = (int)dr["IconID"];
                mayorLocation.MayorID = (int)dr["MayorID"];

            }
            return mayorLocation;

        }


        private static IList<MayorLocation> GetMayorLocationsBySql(string safeSql)
        {
            List<MayorLocation> list = new List<MayorLocation>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, null))
            {
                while (dr.Read())
                {
                    MayorLocation mayorLocation = new MayorLocation();

                    mayorLocation.ID = (int)dr["ID"];
                    mayorLocation.Location = (string)dr["Location"];
                    mayorLocation.IconID = (int)dr["IconID"];
                    mayorLocation.MayorID = (int)dr["MayorID"];

                    list.Add(mayorLocation);
                }
            }
            return list;
        }

        private static IList<MayorLocation> GetMayorLocationsBySql(string safeSql, params SqlParameter[] para)
        {
            List<MayorLocation> list = new List<MayorLocation>();

            using (SqlDataReader dr = DBHelper.ExecuteGetReader(CommandType.Text, safeSql, para))
            {
                while (dr.Read())
                {
                    MayorLocation mayorLocation = new MayorLocation();

                    mayorLocation.ID = (int)dr["ID"];
                    mayorLocation.Location = (string)dr["Location"];
                    mayorLocation.IconID = (int)dr["IconID"];
                    mayorLocation.MayorID = (int)dr["MayorID"];

                    list.Add(mayorLocation);
                }
            }
            return list;
        }
    }
}
